/* Drop the assembly */
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = N'{0}')
BEGIN

/*
{0} : Assambly Name (string)
{1} : Assembly Name (literal)
{2} : Schema Name
{3} : Full Drop? (TRUE or FALSE)
*/

	/* Drop non-assembly objects */
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{2}].[AddGeometryQueryFunctions]') AND type in (N'P', N'PC'))
		DROP PROCEDURE [{2}].[AddGeometryQueryFunctions]
	
	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{2}].[DropGeometryQueryFunctions]') AND type in (N'P', N'PC'))
		DROP PROCEDURE [{2}].[DropGeometryQueryFunctions]

	IF {3} AND EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{2}].[GEOMETRY_COLUMNS]') AND type in (N'U'))
		DROP TABLE [{2}].[GEOMETRY_COLUMNS]

	IF {3} AND EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{2}].[SPATIAL_REF_SYS]') AND type in (N'U'))
		DROP TABLE [{2}].[SPATIAL_REF_SYS]

	DECLARE @error int
	SET @error = 0

	/* Drop the assembly user defined aggregates, triggers, functions and procedures */
	DECLARE @moduleId sysname
	DECLARE @moduleName sysname
	DECLARE @moduleType char(2)
	DECLARE @moduleClass tinyint
	DECLARE @moduleSchemaId int
	DECLARE assemblyModules CURSOR FAST_FORWARD FOR
		SELECT t.object_id, t.name, t.type, t.parent_class as class, NULL as schema_id
			FROM sys.triggers t
			INNER JOIN sys.assembly_modules m ON t.object_id = m.object_id
			INNER JOIN sys.assemblies a ON m.assembly_id = a.assembly_id
			WHERE a.name = N'{0}'
		UNION
		SELECT o.object_id, o.name, o.type, NULL as class, o.schema_id
			FROM sys.objects o
			INNER JOIN sys.assembly_modules m ON o.object_id = m.object_id
			INNER JOIN sys.assemblies a ON m.assembly_id = a.assembly_id
			WHERE a.name = N'{0}'
	OPEN assemblyModules
	FETCH NEXT FROM assemblyModules INTO @moduleId, @moduleName, @moduleType, @moduleClass, @moduleSchemaId
	WHILE (@error = 0 AND @@FETCH_STATUS = 0)
	BEGIN
		DECLARE @dropModuleString nvarchar(256)
		IF (@moduleType = 'AF') SET @dropModuleString = N'AGGREGATE'
		IF (@moduleType = 'TA') SET @dropModuleString = N'TRIGGER'
		IF (@moduleType = 'FT' OR @moduleType = 'FS') SET @dropModuleString = N'FUNCTION'
		IF (@moduleType = 'PC') SET @dropModuleString = N'PROCEDURE'
		IF (@moduleType = 'TA' AND @moduleClass = 0)
		BEGIN
			SET @dropModuleString = N'DROP ' + @dropModuleString + ' [' + REPLACE(@moduleName, ']', ']]') + ']' + N' ON DATABASE'
		END
		ELSE
		BEGIN
			SET @dropModuleString = N'DROP ' + @dropModuleString + ' [' + SCHEMA_NAME(@moduleSchemaId) + '].[' + REPLACE(@moduleName, ']', ']]') + ']'
		END
		EXEC sp_executesql @dropModuleString
		FETCH NEXT FROM assemblyModules INTO @moduleId, @moduleName, @moduleType, @moduleClass, @moduleSchemaId
	END
	CLOSE assemblyModules
	DEALLOCATE assemblyModules
	
	/* Drop the assembly user defined types */
	DECLARE @typeId int
	DECLARE @typeName sysname
	DECLARE @schemaId int
	DECLARE assemblyTypes CURSOR FAST_FORWARD
		FOR SELECT t.user_type_id, t.name, t.schema_id
			FROM sys.assembly_types t
			INNER JOIN sys.assemblies a ON t.assembly_id = a.assembly_id
			WHERE a.name = N'{0}'
	OPEN assemblyTypes
	FETCH NEXT FROM assemblyTypes INTO @typeId, @typeName, @schemaId
	WHILE (@error = 0 AND @@FETCH_STATUS = 0)
	BEGIN
		DECLARE @dropTypeString nvarchar(256)
		SET @dropTypeString = N'DROP TYPE [' + SCHEMA_NAME(@schemaId) + '].[' + REPLACE(@typeName, ']', ']]') + ']'
		EXEC sp_executesql @dropTypeString
		FETCH NEXT FROM assemblyTypes INTO @typeId, @typeName, @schemaId
	END
	CLOSE assemblyTypes
	DEALLOCATE assemblyTypes

	/*  Drop the assembly */
	IF (@error = 0)
		DROP ASSEMBLY [{1}] WITH NO DEPENDENTS
END

IF {3} AND EXISTS (SELECT * FROM sys.schemas WHERE name = N'{2}')
	EXEC sys.sp_executesql N'DROP SCHEMA [{2}]'

USE master

IF {3} AND EXISTS (SELECT name FROM sys.syslogins WHERE name=N'{0}Login')
	DROP LOGIN [{1}Login]

IF {3} AND EXISTS (SELECT name FROM sys.asymmetric_keys WHERE name=N'{0}Key')
	DROP ASYMMETRIC KEY [{1}Key]
